MySQL

Installation

First, download the 'geoserver-XXX-mysql-plugin.zip' from the GeoServer project downloads page: http://sourceforge.net/project/showfiles.php?group_id=25086

Unzip this and place the jarfiles in your GeoServer installation's geoserver/WEB-INF/lib directory, then restart GeoServer. (NOTE: To get our MySQL datastore to 'see' the tables properly, it was necessary to copy the mysql-connector-java-3.0.17.jar file despite Tomcat already having a MySQL connector library - Tomcat's connector was not able to detect 'geometry' columns.) 

Data Set Configuration

The MySQL DataStore should now show up as an option when creating a new DataStore (Welcome -> Config -> Data -> Stores -> New in the web admin). Select it as an option, and fill out the name field. 

The following screen allows you to enter the basic connection details that GeoServer will use to access MySQL:

In this case, our database is called 'vista' and hosted on the local machine.  Make sure the user you specify has read/write access to the tables you intend to source your geospatial data from!

Click 'Submit', then 'Apply' and 'Save' to make the datastore available. 

Table Structure

Before you can create a feature type from your MySQL datastore, you will need to create a database table to contain your geospatial data (assuming you don't have one already).  The following SQL will create an appropriately-typed table:

CREATE TABLE `geoserver_test` (
     `the_geom` geometry NOT NULL,
     `fid` varchar(255) NOT NULL,
     `description` varchar(2000) default NULL,
     PRIMARY KEY  (`fid`)
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1
   

Feature Type Configuration 

In the GeoServer admin console, select (Welcome -> Config -> Data -> FeatureTypes -> New) and select the appropriate table within your MySQL datastore.

You'll need to enter a default style (try 'capitals'), an SRS projection code (try 4326) and click the 'Generate' button to generate an appropriate bounding box if you have existing data in the table.  The other values on this screen should be OK, just click 'Submit', then 'Apply' and 'Save' to make the feature available.

The new feature should now respond with XML if you hit the URL:

 http://localhost:8080/geoserver/wfs?request=getfeature&service=wfs&version=1.0.0&typename=vista:geoserver_test 

:- where 'vista' is the name of the datastore and 'geoserver_test' is the name of your feature type.

 Known Bugs

When creating a new FeatureType be sure that the table has a primary key.
If not then the featureIds returned will not work properly. We have also
not yet figured out how to make the password optional, the jdbc driver seems
to always try to send the password option, even if the password is blank. So
the short answer is to just connect with users who have passwords. If anyone
has any insight into this problem please let us know.

Testing with MySQL seems to reveal that both the WMS and WFS work. MySQL is not SRS aware, which is a blocker in gt2 but I just used 4326, and everything seemed to go fine. A bit of a hiccup with MapPreview stuff, but lead to the fixing of a little bugger in the codebase.

MySQL Feature Data Set Editor.png (image/png)
MySQL Feature Data Set Editor.png (image/png)

Two things I think are worth noting here:  1. The MySQL extension has to be installed separately to the main GeoServer package 2. In MySQL, InnoDB tables can cause crashes when spatial queries are made against them.  The quick fix is to use the MyISAM table engine instead when you are creating tables e.g.: CREATE TABLE DEMOTABLE (i INT) ENGINE = MYISAM; Posted by robwjones at May 08, 2006 12:54

If you get an MySQL syntax error similar to the one below, there is a work around.  This information is based on info from here and applies to my experiences with GeoServer 1.4.0. <begin error message> worldbase:world_table  org.geotools.data.DataSourceException: SQL Error building FeatureType for world_table Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQLserver version for the right syntax to use near ': www.worlddb.com.world_table WHERE 0=1' at line 1" <end error> When setting up the  GeoServer namespace via the web-based administrator interface,  GeoServer requires you to enter a url/uri that starts with 'http://'.  This causes errors like the one above when later attempting to add a MySQL feature type.  To circumvent this problem, you can manually edit the 'catolog.xml' file and manually change the uri for the MySQL namespace.  The link above points you to the catalog.xml file found in geoserver's web-inf directory.  However, I had to modify the catalog.xml file in the 'data-dir' director.  Once you find the correct catalog.xml file, make the following edits: Change the line that reads: <namespace uri = "http://www.myspace.net" prefix = "myspace"  /> To this: <namespace uri = "myspace" prefix = "myspace"  />  In my case, I used the MySQL database name for the uri and prefix variables.  This should work after you restart geoserver.   Good luck! Posted by alexzendel at Feb 25, 2007 10:41